package com.example.project.service.impl;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.example.project.annotation.DataSource;
import com.example.project.config.datasource.DynamicDataSourceContextHolder;
import com.example.project.enums.DataSourceType;
import com.example.project.service.DataSyncService;

import java.util.List;
import java.util.Map;

/**
 * 数据同步服务实现类
 */
@Service
public class DataSyncServiceImpl implements DataSyncService {

    private static final Logger logger = LoggerFactory.getLogger(DataSyncServiceImpl.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    @DataSource(DataSourceType.SYNC)
    public void syncUserData() {
        try {
            logger.info("开始同步用户数据...");
            
            // 从第二数据源查询用户数据
            String querySql = "SELECT user_id, user_name, nick_name, email, phonenumber, sex, avatar, password, salt, status, del_flag, login_ip, login_date, create_by, create_time, update_by, update_time, remark FROM t_user WHERE del_flag = 0";
            List<Map<String, Object>> users = jdbcTemplate.queryForList(querySql);
            
            logger.info("从第二数据源查询到 {} 条用户数据", users.size());
            
            // 切换到主数据源进行插入/更新
            for (Map<String, Object> user : users) {
                syncUserToMaster(user);
            }
            
            logger.info("用户数据同步完成");
        } catch (Exception e) {
            logger.error("同步用户数据失败", e);
            throw new RuntimeException("同步用户数据失败", e);
        }
    }

    @Override
    @DataSource(DataSourceType.SYNC)
    public void syncProjectData() {
        try {
            logger.info("开始同步项目数据...");
            
            // 从第二数据源查询项目数据
            String querySql = "SELECT * FROM t_project WHERE del_flag = 0";
            List<Map<String, Object>> projects = jdbcTemplate.queryForList(querySql);
            
            logger.info("从第二数据源查询到 {} 条项目数据", projects.size());
            
            // 切换到主数据源进行插入/更新
            for (Map<String, Object> project : projects) {
                syncProjectToMaster(project);
            }
            
            logger.info("项目数据同步完成");
        } catch (Exception e) {
            logger.error("同步项目数据失败", e);
            throw new RuntimeException("同步项目数据失败", e);
        }
    }

    /**
     * 同步需求数据（xuqiu → issue）- 分页同步
     */
    @DataSource(DataSourceType.SYNC)
    public void syncXuqiuToIssue() {
        syncXuqiuToIssue(1000); // 默认每页1000条
    }

    /**
     * 同步需求数据（xuqiu → issue）- 可配置分页大小
     * @param pageSize 每页同步的记录数
     */
    @DataSource(DataSourceType.SYNC)
    public void syncXuqiuToIssue(int pageSize) {
        try {
            logger.info("开始分页同步需求数据（xuqiu → issue），每页 {} 条记录...", pageSize);
            
            int currentPage = 0;
            int totalSynced = 0;
            boolean hasMoreData = true;
            
            while (hasMoreData) {
                int offset = currentPage * pageSize;
                
                // 从第二数据源分页查询需求数据
                String querySql = "SELECT xuQiuId, shangChuanUserId, lingQuUserId, keHuId, xuQiuTitle, xuQiuInfoCh, state, type, quanZhong, createDateTime, theLastChangeStateDateTime, muBiaoDate FROM xuqiu WHERE state != 99 LIMIT ? OFFSET ?";
                List<Map<String, Object>> xuqiuList = jdbcTemplate.queryForList(querySql, pageSize, offset);
                
                int pageCount = xuqiuList.size();
                logger.info("第 {} 页同步开始，查询到 {} 条需求数据", currentPage + 1, pageCount);
                
                if (pageCount > 0) {
                    // 手动切换到主数据源进行插入/更新
                    DynamicDataSourceContextHolder.setDataSourceType(DataSourceType.MASTER.name());
                    try {
                        for (Map<String, Object> xuqiu : xuqiuList) {
                            syncXuqiuToIssueToMaster(xuqiu);
                        }
                    } finally {
                        // 切换回第二数据源继续查询
                        DynamicDataSourceContextHolder.setDataSourceType(DataSourceType.SYNC.name());
                    }
                    
                    totalSynced += pageCount;
                    logger.info("第 {} 页同步完成，已同步 {} 条需求数据", currentPage + 1, totalSynced);
                    
                    // 如果查询到的数据量小于pageSize，说明没有更多数据了
                    if (pageCount < pageSize) {
                        hasMoreData = false;
                    }
                    
                    currentPage++;
                    
                    // 每同步一页后短暂休息，避免对数据库造成过大压力
                    if (hasMoreData) {
                        Thread.sleep(100); // 休息100毫秒
                    }
                } else {
                    hasMoreData = false;
                }
            }
            
            logger.info("需求数据分页同步完成，总共同步了 {} 条记录", totalSynced);
        } catch (Exception e) {
            logger.error("同步需求数据失败", e);
            throw new RuntimeException("同步需求数据失败", e);
        } finally {
            // 确保清理数据源上下文
            DynamicDataSourceContextHolder.clearDataSourceType();
        }
    }

    /**
     * 同步客户数据（kehu → project）
     */
    @DataSource(DataSourceType.SYNC)
    public void syncKehuToProject() {
        try {
            logger.info("开始同步客户数据（kehu → project）...");
            
            // 从第二数据源查询客户数据（使用原始字段名，在同步方法中处理映射）
            String querySql = "SELECT keHuId, keHuNameKr, keHuNameP, state, keHuColor, keHuInfo, yunYingType, gitInfo, fuWuQiInfo, yuMing, backOfficeYuMing, jintTaiYuMing, qianYueDate, daoQiDate, fuWuQiDaoQiDate, qianYueInfo, apiBeiZhu, beiZhu, yuYanType FROM kehu WHERE state != 99";
            List<Map<String, Object>> kehuList = jdbcTemplate.queryForList(querySql);
            
            logger.info("从第二数据源查询到 {} 条客户数据", kehuList.size());
            
            // 手动切换到主数据源进行插入/更新
            DynamicDataSourceContextHolder.setDataSourceType(DataSourceType.MASTER.name());
            try {
                for (Map<String, Object> kehu : kehuList) {
                    syncKehuToProjectToMaster(kehu);
                }
            } finally {
                DynamicDataSourceContextHolder.clearDataSourceType();
            }
            
            logger.info("客户数据同步完成");
        } catch (Exception e) {
            logger.error("同步客户数据失败", e);
            throw new RuntimeException("同步客户数据失败", e);
        }
    }

    /**
     * 同步娱乐场数据（casinolist → t_company）
     */
    @DataSource(DataSourceType.SYNC)
    public void syncCasinoToCompany() {
        try {
            logger.info("开始同步娱乐场数据（casinolist → t_company）...");
            
            // 从第二数据源查询娱乐场数据
            String querySql = "SELECT casinoListId, keHuId as project_id, gongSiName as company_name, gongSiUrl as company_url, suoShuShangJia as merchant_name, zhangHao as account, chouMaYuAnJia as estim_price, chouMaJiaGe as actual_price FROM casinolist";
            List<Map<String, Object>> casinoList = jdbcTemplate.queryForList(querySql);
            
            logger.info("从第二数据源查询到 {} 条娱乐场数据", casinoList.size());
            
            // 手动切换到主数据源进行插入/更新
            DynamicDataSourceContextHolder.setDataSourceType(DataSourceType.MASTER.name());
            try {
                for (Map<String, Object> casino : casinoList) {
                    syncCasinoToCompanyToMaster(casino);
                }
            } finally {
                DynamicDataSourceContextHolder.clearDataSourceType();
            }
            
            logger.info("娱乐场数据同步完成");
        } catch (Exception e) {
            logger.error("同步娱乐场数据失败", e);
            throw new RuntimeException("同步娱乐场数据失败", e);
        }
    }

    @Override
    @Transactional
    public void syncAllData() {
        logger.info("开始同步所有数据...");
        
        // 先同步客户数据（kehu → project）
        syncKehuToProject();
        
        // 再同步需求数据（xuqiu → issue），因为issue表依赖project表
        syncXuqiuToIssue();
        
        // 最后同步娱乐场数据（casinolist → t_company），因为t_company表依赖project表
        syncCasinoToCompany();
        
        logger.info("所有数据同步完成");
    }

    @Override
    @DataSource(DataSourceType.SYNC)
    public boolean checkSyncDataSourceConnection() {
        try {
            String testSql = "SELECT 1";
            jdbcTemplate.queryForObject(testSql, Integer.class);
            logger.info("第二数据源连接正常");
            return true;
        } catch (Exception e) {
            logger.error("第二数据源连接失败", e);
            return false;
        }
    }

    /**
     * 同步用户数据到主数据库
     */
    @DataSource(DataSourceType.MASTER)
    private void syncUserToMaster(Map<String, Object> user) {
        try {
            // 检查用户是否已存在
            String checkSql = "SELECT COUNT(*) FROM t_user WHERE user_id = ?";
            Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, user.get("user_id"));
            
            if (count > 0) {
                // 更新用户数据
                String updateSql = "UPDATE t_user SET user_name = ?, nick_name = ?, email = ?, phonenumber = ?, sex = ?, avatar = ?, password = ?, salt = ?, status = ?, del_flag = ?, login_ip = ?, login_date = ?, update_by = ?, update_time = ?, remark = ? WHERE user_id = ?";
                jdbcTemplate.update(updateSql, 
                    user.get("user_name"), user.get("nick_name"), user.get("email"), 
                    user.get("phonenumber"), user.get("sex"), user.get("avatar"), 
                    user.get("password"), user.get("salt"), user.get("status"), 
                    user.get("del_flag"), user.get("login_ip"), user.get("login_date"), 
                    user.get("update_by"), user.get("update_time"), user.get("remark"), 
                    user.get("user_id"));
                logger.debug("更新用户: {}", user.get("user_name"));
            } else {
                // 插入新用户数据
                String insertSql = "INSERT INTO t_user (user_id, user_name, nick_name, email, phonenumber, sex, avatar, password, salt, status, del_flag, login_ip, login_date, create_by, create_time, update_by, update_time, remark) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                jdbcTemplate.update(insertSql,
                    user.get("user_id"), user.get("user_name"), user.get("nick_name"), 
                    user.get("email"), user.get("phonenumber"), user.get("sex"), 
                    user.get("avatar"), user.get("password"), user.get("salt"), 
                    user.get("status"), user.get("del_flag"), user.get("login_ip"), 
                    user.get("login_date"), user.get("create_by"), user.get("create_time"), 
                    user.get("update_by"), user.get("update_time"), user.get("remark"));
                logger.debug("插入用户: {}", user.get("user_name"));
            }
        } catch (Exception e) {
            logger.error("同步用户数据失败: {}", user.get("user_name"), e);
            throw e;
        }
    }

    /**
     * 同步项目数据到主数据库
     */
    @DataSource(DataSourceType.MASTER)
    private void syncProjectToMaster(Map<String, Object> project) {
        try {
            // 这里根据实际的项目表结构进行调整
            String tableName = "t_project";
            String primaryKey = "project_id"; // 假设主键字段名
            
            // 检查项目是否已存在
            String checkSql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + primaryKey + " = ?";
            Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, project.get(primaryKey));
            
            if (count > 0) {
                // 构建更新SQL
                StringBuilder updateSql = new StringBuilder("UPDATE " + tableName + " SET ");
                StringBuilder params = new StringBuilder();
                
                for (Map.Entry<String, Object> entry : project.entrySet()) {
                    if (!entry.getKey().equals(primaryKey)) {
                        if (params.length() > 0) {
                            updateSql.append(", ");
                            params.append(", ");
                        }
                        updateSql.append(entry.getKey()).append(" = ?");
                        params.append("?");
                    }
                }
                
                updateSql.append(" WHERE ").append(primaryKey).append(" = ?");
                
                jdbcTemplate.update(updateSql.toString(), project.values().toArray());
                logger.debug("更新项目: {}", project.get("project_name"));
            } else {
                // 构建插入SQL
                StringBuilder insertSql = new StringBuilder("INSERT INTO " + tableName + " (");
                StringBuilder valuesSql = new StringBuilder(") VALUES (");
                StringBuilder params = new StringBuilder();
                
                for (Map.Entry<String, Object> entry : project.entrySet()) {
                    if (insertSql.length() > tableName.length() + 2) {
                        insertSql.append(", ");
                        valuesSql.append(", ");
                        params.append(", ");
                    }
                    insertSql.append(entry.getKey());
                    valuesSql.append("?");
                    params.append("?");
                }
                
                insertSql.append(valuesSql).append(")");
                
                jdbcTemplate.update(insertSql.toString(), project.values().toArray());
                logger.debug("插入项目: {}", project.get("project_name"));
            }
        } catch (Exception e) {
            logger.error("同步项目数据失败", e);
            throw e;
        }
    }

    /**
     * 同步需求数据到主数据库（xuqiu → issue）
     */
    private void syncXuqiuToIssueToMaster(Map<String, Object> xuqiu) {
        try {
            // 处理null值，如果project_id为null就设置为0
            Object projectId = xuqiu.get("keHuId");
            if (projectId == null) {
                projectId = 0;
            }
            
            // 检查需求是否已存在
            String checkSql = "SELECT COUNT(*) FROM t_issue WHERE id = ?";
			Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, xuqiu.get("xuQiuId"));

			if (count > 0) {
				// 更新需求数据（字段映射：第二数据源字段 → 主数据库字段）
				String updateSql = "UPDATE t_issue SET create_by = ?, assigned_to_id = ?, priority_id = ?, project_id = ?, subject = ?, description = ?, status_id = ?, tracker_id = ?, create_time = ?, update_time = ?, due_date = ? WHERE id = ?";
				jdbcTemplate.update(updateSql, xuqiu.get("shangChuanUserId"), xuqiu.get("lingQuUserId"),
						xuqiu.get("quanZhong"), projectId, xuqiu.get("xuQiuTitle"), xuqiu.get("xuQiuInfoCh"),
						xuqiu.get("state"), xuqiu.get("type"), xuqiu.get("createDateTime"),
						xuqiu.get("theLastChangeStateDateTime"), xuqiu.get("muBiaoDate"), xuqiu.get("xuQiuId"));
				logger.debug("更新需求: {}", xuqiu.get("xuQiuTitle"));
			} else {
				// 插入新需求数据（字段映射：第二数据源字段 → 主数据库字段）
				String insertSql = "INSERT INTO t_issue (id, create_by, assigned_to_id, project_id,priority_id, subject, description, status_id, tracker_id, create_time, update_time, due_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
				jdbcTemplate.update(insertSql, xuqiu.get("xuQiuId"), xuqiu.get("shangChuanUserId"),
						xuqiu.get("lingQuUserId"), projectId, xuqiu.get("quanZhong"),
						xuqiu.get("xuQiuTitle") != null ? xuqiu.get("xuQiuTitle") : "无标题", xuqiu.get("xuQiuInfoCh"),
						xuqiu.get("state"), xuqiu.get("type"), xuqiu.get("createDateTime"),
						xuqiu.get("theLastChangeStateDateTime"), xuqiu.get("muBiaoDate"));
				logger.debug("插入需求: {}", xuqiu.get("xuQiuTitle"));
			}
		} catch (Exception e) {
			logger.error("同步需求数据失败: {}", xuqiu.get("xuQiuTitle"), e);
            throw e;
        }
    }

    /**
     * 同步客户数据到主数据库（kehu → project）
     */
    @DataSource(DataSourceType.MASTER)
    private void syncKehuToProjectToMaster(Map<String, Object> kehu) {
        try {
            // 检查客户是否已存在
            String checkSql = "SELECT COUNT(*) FROM t_project WHERE id = ?";
            Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, kehu.get("keHuId"));
            
            if (count > 0) {
                // 更新客户数据
                String updateSql = "UPDATE t_project SET name = ?, customer_name = ?, state = ?, color = ?, customer_detail = ?, operation_type = ?, git_url = ?, server_detail = ?, domain = ?, admin_domain = ?, html_domain = ?, sign_date = ?, expir_date = ?, server_expir_date = ?, sign_detail = ?, description = ?, remark = ?, develop_language = ? WHERE id = ?";
                jdbcTemplate.update(updateSql, 
                    kehu.get("keHuNameKr"), kehu.get("keHuNameP"), kehu.get("state"),
                    kehu.get("keHuColor"), kehu.get("keHuInfo"), kehu.get("yunYingType"),
                    kehu.get("gitInfo"), kehu.get("fuWuQiInfo"), kehu.get("yuMing"),
                    kehu.get("backOfficeYuMing"), kehu.get("jintTaiYuMing"), kehu.get("qianYueDate"),
                    kehu.get("daoQiDate"), kehu.get("fuWuQiDaoQiDate"), kehu.get("qianYueInfo"),
                    kehu.get("apiBeiZhu"), kehu.get("beiZhu"), kehu.get("yuYanType"), kehu.get("keHuId"));
                logger.debug("更新客户: {}", kehu.get("keHuNameKr"));
            } else {
                // 插入新客户数据
                String insertSql = "INSERT INTO t_project (id, name, customer_name, state, color, customer_detail, operation_type, git_url, server_detail, domain, admin_domain, html_domain, sign_date, expir_date, server_expir_date, sign_detail, description, remark, develop_language) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                jdbcTemplate.update(insertSql,
                    kehu.get("keHuId"), kehu.get("keHuNameKr"), kehu.get("keHuNameP"),
                    kehu.get("state"), kehu.get("keHuColor"), kehu.get("keHuInfo"),
                    kehu.get("yunYingType"), kehu.get("gitInfo"), kehu.get("fuWuQiInfo"),
                    kehu.get("yuMing"), kehu.get("backOfficeYuMing"), kehu.get("jintTaiYuMing"),
                    kehu.get("qianYueDate"), kehu.get("daoQiDate"), kehu.get("fuWuQiDaoQiDate"),
                    kehu.get("qianYueInfo"), kehu.get("apiBeiZhu"), kehu.get("beiZhu"), kehu.get("yuYanType"));
                logger.debug("插入客户: {}", kehu.get("keHuNameKr"));
            }
        } catch (Exception e) {
            logger.error("同步客户数据失败: {}", kehu.get("keHuNameKr"), e);
            throw e;
        }
    }

    /**
     * 同步娱乐场数据到主数据库（casinolist → t_company）
     */
    @DataSource(DataSourceType.MASTER)
    private void syncCasinoToCompanyToMaster(Map<String, Object> casino) {
        try {
            // 检查娱乐场是否已存在
            String checkSql = "SELECT COUNT(*) FROM t_company WHERE id = ?";
            Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, casino.get("casinoListId"));
            
            if (count > 0) {
                // 更新娱乐场数据
                String updateSql = "UPDATE t_company SET project_id = ?, company_name = ?, company_url = ?, merchant_name = ?, account = ?, estim_price = ?, actual_price = ? WHERE id = ?";
                jdbcTemplate.update(updateSql, 
                    casino.get("project_id"), casino.get("company_name"), casino.get("company_url"),
                    casino.get("merchant_name"), casino.get("account"), casino.get("estim_price"),
                    casino.get("actual_price"), casino.get("casinoListId"));
                logger.debug("更新娱乐场: {}", casino.get("company_name"));
            } else {
                // 插入新娱乐场数据
                String insertSql = "INSERT INTO t_company (id, project_id, company_name, company_url, merchant_name, account, estim_price, actual_price) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
                jdbcTemplate.update(insertSql,
                    casino.get("casinoListId"), casino.get("project_id"), casino.get("company_name"),
                    casino.get("company_url"), casino.get("merchant_name"), casino.get("account"),
                    casino.get("estim_price"), casino.get("actual_price"));
                logger.debug("插入娱乐场: {}", casino.get("company_name"));
            }
        } catch (Exception e) {
            logger.error("同步娱乐场数据失败: {}", casino.get("company_name"), e);
            throw e;
        }
    }

    /**
     * 获取第二数据源的表结构信息
     */
    @DataSource(DataSourceType.SYNC)
    public List<Map<String, Object>> getTableInfo(String tableName) {
        String sql = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'xuqiuplusdatabase' AND TABLE_NAME = ?";
        return jdbcTemplate.queryForList(sql, tableName);
    }
}